﻿using ConfExpert.Enums;
using DevTeam;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace ConfExpert.Forms.Sistem.Conversion
{
    public static class Dagilim
    {
        public static DataTable Convert(ProcessMethod Method, string Path)
        {
            //Assumptions:
            //Satırlar okunmaya başladıktan sonra B sütununda değer okunmazsa o dosyanın işlenecek satırları bitmiş demektir
            //Beden boyları (label) tamsayıdır
            //Dağılım erkektir

            //IExcelDataReader excelReader = null;
            List<string> types = new List<string>();

            DataTable dtSablon = new DataTable();

            //foreach (string label in "32	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	XXS	XS	S	M	L	XL	XXL	3XL	4XL	5XL	6XL".Split('\t'))
            //    dtSablon.Columns.Add("" + label, typeof(int));

            //foreach (string label in "32	34	36	38	40	42	44	46	48	50	52	54	56	58	60	62".Split('\t'))
            //    dtSablon.Columns.Add("B" + label, typeof(int));

            //foreach (string label in "ST".Split('\t'))
            //    dtSablon.Columns.Add(label, typeof(int));

            dtSablon.Columns.Add("DosyaAdi", typeof(String));
            //dtSablon.Columns.Add("Season", typeof(String));
            //dtSablon.Columns.Add("Delivery", typeof(String));

            DataTable dtLabels = new DataTable();
            for (int i = 0; i < 50; i++)
                dtLabels.Columns.Add("col" + i.ToString(), typeof(string));

            dtLabels.Rows.Add(dtLabels.NewRow());



            if (Method == ProcessMethod.XlsFolder)
                foreach (string fileName in Directory.GetFiles(Path, "*.xls", SearchOption.AllDirectories))
                    ProcessOne(dtSablon, dtLabels, fileName);
            else
                ProcessOne(dtSablon, dtLabels, Path);

            return dtSablon;

            //gridControl1.DataSource = dtSablon;
            //gridControl1.ExportToXlsx("d:\\tt.xlsx");

        }

        private static void ProcessOne(DataTable dtSablon, DataTable dtLabels, string fileName)
        {
            //FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
            //IExcelDataReader excel = ExcelReaderFactory.CreateBinaryReader(stream);
            //DataTable dt = excel.AsDataSet().Tables[0];
            ////for (int i = 1; i < excel.AsDataSet().Tables.Count; i++)
            ////    dt.Merge(excel.AsDataSet().Tables[i]);
            //if (excel.AsDataSet().Tables.Contains("Excel"))
            //    dt = excel.AsDataSet().Tables["Excel"];

            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(fileName);
            System.Data.DataSet dataSet = workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);
            DataTable dt = dataSet.Tables[0];
            if (dataSet.Tables.Contains("Excel"))
                dt = dataSet.Tables["Excel"]; 

            int headerPos = 0;
            string orderNo = string.Empty;
            for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
            {
                if (dt.Rows[rowIndex][0].ToString().ToLower().Trim() != "pos")
                    continue;

                for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                {
                    string cellValue4 = dt.Rows[rowIndex][colIndex].ToStr();
                    if (cellValue4 != string.Empty)
                    {
                        dtLabels.Rows[0][colIndex] = cellValue4;
                        if (!dtSablon.Columns.Contains(cellValue4))
                            dtSablon.Columns.Add(cellValue4, typeof(string));
                    }
                }
                headerPos = rowIndex;
                break;
            }

            string str = string.Empty;


            for (int rowIndex = headerPos + 1; rowIndex < dt.Rows.Count; rowIndex++)
            {
                if (dt.Rows[rowIndex][0].ToString().ToLower().Trim() == "Pos")
                    continue;

                string firstColumnCell = dt.Rows[rowIndex][0].ToStr();
                if (firstColumnCell == string.Empty)
                    break;

                DataRow dr = dtSablon.NewRow();
                dr["DosyaAdi"] = Path.GetFileName(fileName);


                for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                {
                    //int dummy;
                    string cellValue = dt.Rows[rowIndex][colIndex].ToStr();
                    string label = dtLabels.Rows[0][colIndex].ToString();

                    if (label == "Pos")
                        if (cellValue != string.Empty)
                            orderNo = cellValue;
                        else
                            cellValue = orderNo;


                    //if (int.TryParse(label, out dummy))
                    //{
                    //    bool bayan = (int.Parse(dr["Modell No."].ToString()) / 10000) == 1;
                    //    label = (bayan ? "B" : "E") + label;
                    //}

                    if (label != string.Empty && cellValue != string.Empty)
                    {
                        if (label == "Pos")
                            dr[label] = cellValue != null ? cellValue : orderNo;    //cellValue.PadLeft(3, '0');
                        else if (label == "col")
                            dr[label] = cellValue.PadLeft(3, '0');
                        else if (label == "part 1 or 2")
                            dr[label] = cellValue.Substring(cellValue.Length - 1);
                        else if (label == "PA Date")
                            dr[label] = DateTime.FromOADate(double.Parse(cellValue)).ToString("dd.MM.yyyy");
                        else if (!(label == "prod. price" && dr[label] != DBNull.Value))
                            dr[label] = cellValue;
                    }
                }

                dtSablon.Rows.Add(dr);
            }
        }
    }
}
